<?php
session_start();
unset($_SESSION['ids']);
header("Content-type: text/html; charset=utf-8");
define('FILE_MAX_SIZE', '10240');	//文件最大允许10m
define('TMP_DIR', realpath('.').'/tmp/');	//临时文件存放位置
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

$mysql1Keys = array(
	'A'=>'user_realname',
	'B'=>'user_sex',
	'C'=>'user_birthday',
	'D'=>'user_zjlx',
	'E'=>'user_zjbh',
	'F'=>'user_xl',
	'G'=>'user_zy',
	'H'=>'user_zylx',
	'I'=>'user_zc',
	'J'=>'user_gzjl',
	'K'=>'user_address',
	'L'=>'user_email',
	'M'=>'user_postcode',
	'N'=>'user_sheng',
	'O'=>'user_mobile',
	'P'=>'user_tel',
	);

$mysql2Keys = array(
	'A'=>'sq_user',
	'B'=>'sq_userid',
	'C'=>'sq_kslx',
	'D'=>'sq_lb',
	'E'=>'sq_xm',
	'F'=>'sq_ksjb',
	'G'=>'sq_qzrq',
	'H'=>'sq_ksrq',
	'I'=>'sq_rq',
	'J'=>'bkbz',
	'K'=>'czbz',
	'L'=>'xs',
	);

$mysql3Keys = array(
	'C'=>'czxm',
	'D'=>'czxmjb',
	'E'=>'czbz',
	'F'=>'czrq',
	'G'=>'fzjg',
	);

function getData(){
	global $PHPExcel;
	global $mysql1Keys;
	global $mysql2Keys;
	global $mysql3Keys;
	$sql = "";
	//数据库连接配置
	$db = mysql_connect('localhost','root','')or die('cant connect mysql!!!');
	mysql_select_db('test_jz_jyjc',$db);
	mysql_query('SET NAMES utf8');


	/**读取excel文件中的第一个工作表*/
	$sheetNum = 0;
	while($sheetNum<3){
		$currentSheet = $PHPExcel->getSheet($sheetNum);
		/**取得最大的列号*/
		$allColumn = $currentSheet->getHighestColumn();
		/**取得一共有多少行*/
		$allRow = $currentSheet->getHighestRow();
		/**从第二行开始输出，因为excel表中第一行为列名*/
		for($currentRow = 2;$currentRow <= $allRow;$currentRow++){
			$sql = "";
			switch ($sheetNum) {
				case 0:
				$table = implode(",",$mysql1Keys);
				$sql .="INSERT INTO userinfo1({$table}) ";
				break;

				case 1:
				$table = implode(",",$mysql2Keys);
				$sql .="INSERT INTO sq_jc({$table}) ";
				break;
				case 2:
				$table = 'userid,'.implode(",",$mysql3Keys);
				$sql .="INSERT INTO pdtj_fj({$table}) ";
				break;
			}

			$sql .=" VALUES(";
				$data = array();
				/**从第A列开始输出*/
				for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){
					$val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();/**ord()将字符转为十进制数*/
					/**如果输出汉字有乱码，则需将输出内容用iconv函数进行编码转换，如下将gb2312编码转为utf-8编码输出*/
				// iconv('utf-8','gb2312', $val) 
					switch ($sheetNum) {
						case 0:
						$data[$mysql1Keys[$currentColumn]] = "'". $val."'";
						break;
						case 1:
						if($currentColumn == 'B') $data['sq_userid'] = $_SESSION['ids']["'".$currentSheet->getCellByColumnAndRow(ord('B') - 65,$currentRow)->getValue()."'"];
						if(isset($mysql2Keys[$currentColumn]) && $currentColumn!='B'){
							$data[$mysql2Keys[$currentColumn]] = "'". $val."'";
						}
						break;

						case 2:
						$data['userid'] = $_SESSION['ids']["'".$currentSheet->getCellByColumnAndRow(ord('B') - 65,$currentRow)->getValue()."'"];
						if(isset($mysql3Keys[$currentColumn])){
							$data[$mysql3Keys[$currentColumn]] = "'". $val."'";
						}
						break;
					}
				}
				$sql.=implode(",",$data).")";
mysql_query($sql);
if($sheetNum == 0){
	// echo $currentSheet->getCellByColumnAndRow(ord('D') - 65,$currentRow)->getValue();
	$_SESSION['ids']["'".$currentSheet->getCellByColumnAndRow(ord('E') - 65,$currentRow)->getValue()."'"] = mysql_insert_id();
		}
		if($sheetNum == 0){
			// arsort($_SESSION['ids']);	//降序一次
			// var_dump($_SESSION['ids']);
		}
	}
	$sheetNum ++ ;
}

mysql_close($db);
}

//================調用處和View========================


if(isset($_POST['button'])){
	// session_start();
	//将excel中的文件，读取为array数组方便循环处理
	/** Include PHPExcel_IOFactory */
	require_once 'PHPExcel/IOFactory.php';
	// echo TMP_DIR.session_id().'.xls';
	// echo '<br/>';
	echo $filePath = (file_exists(TMP_DIR.session_id().'.xls')) ? TMP_DIR.session_id().'.xls' : TMP_DIR.session_id().'.xlsx';
	// if(!file_exists($filePath)) die('无法找到文件'.$filePath);
	$PHPExcel = new PHPExcel();
	/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
	$PHPReader = new PHPExcel_Reader_Excel2007();
	if(!$PHPReader->canRead($filePath)){
		$PHPReader = new PHPExcel_Reader_Excel5();
		if(!$PHPReader->canRead($filePath)){
			echo 'no Excel';
			return ;
		}
	}

	$PHPExcel = $PHPReader->load($filePath);
	getData();
	header("Location:success.php");
}else{
	// session_write_close();
	// $session_id = $_GET['sessionid'];
	// session_id($session_id);
	// session_start();
	//upload file
	$fileArr = $_FILES['Filedata'];
	$filename = $fileArr['name'];
	$tmpFile = $fileArr['tmp_name'];
	$fileSize = $fileArr['size'];
	$ext = substr($filename,-3);
	if($fileArr['error']!=0){
		die('上传发送错误！');
	}
	// if($fileSize > FILE_MAX_SIZE){
	// 	die('文件太大，请上传小于10m的Excel文件!')
	// }

	if($ext != 'xls' && $ext != "xlsx"){
		die('文件格式不对！请上传Excel文件！');
	}

	//转移到临时目录-暂时没有必要

	//读取文件
	copy($tmpFile, TMP_DIR.session_id().'.'.$ext);
	echo 'ok';
}
